﻿using System.Collections.Generic;
using System.Threading.Tasks;
using Dapper;
using MySql.Data.MySqlClient;

namespace ChinaMobile.XinJu
{
    public class XinJuStationStore
    {
        private readonly MySqlConnection _conn;


        public XinJuStationStore(MySqlConnection conn)
        {
            _conn = conn;
            _conn.Open();
        }

        public Task<IEnumerable<FamilyStation>> ListFamily(int page, int pageSize)
        {
            var start = (page - 1) * pageSize;
            var end = page * pageSize;
            var str = $"SELECT * FROM wdfxpt.v_集家客_预覆盖_家客规划选址 where `编号` != '' LIMIT {start}, {end}";

            return _conn.QueryAsync<FamilyStation>(str);
        }

        public Task<IEnumerable<GroupStation>> ListGroup(int page, int pageSize)
        {
            var start = (page - 1) * pageSize;
            var end = page * pageSize;
            var str = $@"SELECT * FROM  wdfxpt.v_集家客_预覆盖_集客规划选址 b where `编号` != '' 
                            and b.编号 not in (SELECT a.编号 FROM wdfxpt.v_集家客_预覆盖_集客规划选址 a
                                             inner join wdfxpt.v_集家客_预覆盖_家客规划选址 b
                                        on wdfxpt.a.编号= wdfxpt.b .编号)  
                        LIMIT {start}, {end}";

            return _conn.QueryAsync<GroupStation>(str);
        }

        public GroupStation Get(string code)
        {
            var str = @"SELECT * FROM  wdfxpt.v_集家客_预覆盖_集客规划选址 b where `编号` != '' 
                            and b.编号 not in (SELECT a.编号 FROM wdfxpt.v_集家客_预覆盖_集客规划选址 a
                                             inner join wdfxpt.v_集家客_预覆盖_家客规划选址 b
                                        on wdfxpt.a.编号= wdfxpt.b .编号)  
where b.编号='" + code + @"' LIMIT 1";
            return _conn.QueryFirst<GroupStation>(str);
        }
    }
}